package com.byb.server.repository;

import com.byb.server.domain.Rate;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import org.springframework.data.jpa.repository.*;

import java.util.List;


/**
 * Spring Data JPA repository for the Rate entity.
 */
@SuppressWarnings("unused")
@Repository
public interface RateRepository extends JpaRepository<Rate, Long> {

    @Query("select r  from Rate r where r.age=:age AND r.premiumPaymentPeriod=:premiumPaymentPeriod AND r.sex.sexName=:sexName group by r.product order by r.price asc ")
    Page<Rate> findByAgeAndPremiumPaymentPeriodAndSexSexName(@Param("age") Integer age, @Param("premiumPaymentPeriod") Integer premiumPaymentPeriod, @Param("sexName") String sexName, Pageable pageable);

    @Query("select r  from Rate r where r.age=:age AND r.premiumPaymentPeriod=:premiumPaymentPeriod AND r.sex.sexName=:sexName group by r.product order by r.price asc ")
    List<Rate> findByAgeAndPremiumPaymentPeriodAndSexSexNameNoPage(@Param("age") Integer age, @Param("premiumPaymentPeriod") Integer premiumPaymentPeriod, @Param("sexName") String sexName);

    @Query(value = "select r.*  from rate r where r.age=:age AND r.premium_payment_period=:premiumPaymentPeriod AND r.sex_id=:sexName group by r.product_id order by r.price asc LIMIT 1", nativeQuery = true)
    Rate findAllByAgeAndPremiumPaymentPeriodAndSexAndAgeAfter(@Param("age") Integer age, @Param("premiumPaymentPeriod") Integer premiumPaymentPeriod, @Param("sexName") Long sexName);

//    @Query(value = "SELECT b.rownum FROM (select (@rownum \\:=@rownum+1)  AS rownum ,r.*  from rate r , (select  @rownum \\:=0) a where r.age=:age AND r.premium_payment_period=:premiumPaymentPeriod AND r.sex_id=:sexName  group by r.product_id order by r.price asc) b WHERE b.product_id=:id", nativeQuery = true)
//    int findOneByAgeAndPremiumPaymentPeriodAndSexSexName(@Param("age") Integer age, @Param("premiumPaymentPeriod") Integer premiumPaymentPeriod, @Param("sexName") Long sexName, @Param("id") Long id);

    Rate findByProductIdAndAgeAndPremiumPaymentPeriodAndSexId(Long id, Integer age, Integer premiumPaymentPeriod, Long sex);
}
